<?php 
$type_condition = "";
 if($type_id)
 {
 	$type_condition = " and type_id = $type_id";
 	
 }
 
 $place_condition = "";
 if($place_id)
 {
 	$place_condition = " and place_id = $place_id ";
 }
 
 	?>      
  
<?php 
$rows = $this->db->query("select * from meterial_base_categories order    by sort_order asc ")->result_array();
foreach($rows as $base){
$sql ="SELECT
material_categories.category_name,
material_categories.cat_base_id,
material_categories.category_id,


(select  COALESCE(sum( amount_total  ),0 ) from inventory_views where material_categories.category_id =  inventory_views.category_id ) as total
,(select  COALESCE(sum( allocation_total  ),0 ) from inventory_views where material_categories.category_id =  inventory_views.category_id ) as allocation_total

FROM
material_categories where 1 $type_condition and cat_base_id = ".$base['cat_base_id'];
$cats =  $this->db->query($sql)->result_array(); ?>

<table class="list" >
          <tbody><tr class=" odd">
            <th style="width:300px;" ><?php echo $base["cat_base_name"]; ?></th>
            
            <th style="width:100px;">จำนวนที่ต้องการ</th>
          
          </tr>
         <?php
          $i = 1;
          foreach($cats as $row){ ?>
        
          <tr class="<?php if($i++%2){ echo "even";} ?>" >
            
            
            <td><?php echo $row['category_name']; ?></td>
            <td>
            <?php
$sql ="select sum(amount) as  amount ,category_id from material_request_items 
where category_id = ".$row["category_id"]." and
mr_id in (select mr_id from material_requests where 1 $place_condition  and  date_format(create_date,'%Y') = '2012') ";
  $rows2 =   $this->db->query($sql)->result_array();
  if( $rows2[0]["amount"])
  {
	echo $rows2[0]["amount"];
  }
  else
  {
  	 echo "0";
  }
//print_r($rows2)
            ?>
            
            </td> 
           
          </tr>
          <?php } } ?>
        </tbody></table>
